package cn.ljy.uem.service.impl;

import cn.hutool.core.map.BiMap;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.ljy.authority.dao.mapper.SysRoleMapper;
import cn.ljy.authority.dao.mapper.SysUserMapper;
import cn.ljy.authority.dao.mapper.SysUserRoleMapper;
import cn.ljy.authority.model.SysRole;
import cn.ljy.authority.model.SysUser;
import cn.ljy.authority.model.SysUserRole;
import cn.ljy.authority.service.SysUserService;
import cn.ljy.common.exception.model.MyExcelException;
import cn.ljy.common.exception.model.MyServiceException;
import cn.ljy.common.exception.model.base.MyRollBackException;
import cn.ljy.common.model.result.ExcelImportResult;
import cn.ljy.common.utils.password.SHA;
import cn.ljy.common.utils.table.ExcelExceptionMessage;
import cn.ljy.common.utils.table.MyHutoolCustomExcelUtil;
import cn.ljy.common.utils.table.MyTableUtil;
import cn.ljy.uem.dao.SchoolClassMapper;
import cn.ljy.uem.dao.SchoolStudentMapper;
import cn.ljy.uem.model.SchoolClass;
import cn.ljy.uem.model.SchoolStudent;
import cn.ljy.uem.model.query.SchoolStudentQuery;
import cn.ljy.uem.service.StudentExcelService;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.AccessDeniedException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.validation.annotation.Validated;

import javax.validation.constraints.NotNull;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 要在一般业务类只上构建的
 */
@Service
@Validated
@Slf4j
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT, timeout = 36000, rollbackFor = MyRollBackException.class)
public class IStudentExcelService implements StudentExcelService {

    //性别
    private final static HashMap<Integer, String> SEX_MAP = new HashMap<Integer, String>() {
        {
            put(0, "女");
            put(1, "男");
            put(2, "保密");
        }
    };
    //表格标题栏和实体的映射,必须为有序map集合
    private final static HashMap STUDENTBEAN_MAP = new LinkedHashMap<String, String>() {
        {
            put("id", "用户编号");
            put("className", "班级名称");
            put("studentNumber", "学号");
            put("name", "名字");
            put("sex", "性别");
            put("national", "民族");
            put("nativePlace", "籍贯");
            put("address", "地址");
            put("tel", "电话");
            put("identityCard", "身份证号");
            put("description", "账户描述");
            put("createTime", "创建时间");
            put("updateTime", "最后编辑时间");
        }
    };
    // 判断是否超级用户
    @Autowired
    private SysUserService sysUserService;
    @Autowired
    private SchoolStudentMapper schoolStudentMapper;
    @Autowired
    private SchoolClassMapper schoolClassMapper;
    @Autowired
    private SysUserMapper sysUserMapper;
    @Autowired
    private SysRoleMapper sysRoleMapper;
    @Autowired
    private SysUserRoleMapper sysUserRoleMapper;
    private String sheetName = "学生表格";//要查找的账户子表

    /**
     * 将user对象转换为要显示的表格行记录
     * <p>
     * //     * @paramuser       应该是从数据库里面读取出来的对象。如果是新建对象，应该包含必须的属性（sex、status）
     *
     * @param isTemplate 是否是模板。
     * @return
     */
    private StudentRow convertStudentRowToExcelRow(SchoolStudent schoolStudent, boolean isTemplate) {
        StudentRow row = null;
        if (schoolStudent != null) {
            //定义行记录对象
            row = new StudentRow();
            //属性拷贝,会将相同属性的值拷贝给新对象
            BeanUtils.copyProperties(schoolStudent, row);
            //设置性别的标签
            row.setSex(SEX_MAP.get(schoolStudent.getSex()));
        }

        if (isTemplate) {//如果是示例
            //从数据库读取所有部门中文名
            List<String> classNames = schoolClassMapper.getClassname();
            if (classNames.size() > 0) {//默认选择的部门
                row.setClassName(classNames.get(0));
            }
        } else {
            //设置部门信息
            if (schoolStudent.getClassId() != null) {//如果部门存在
                SchoolClass schoolClass = new SchoolClass();
                schoolClass = schoolClassMapper.findById(schoolStudent.getClassId());
                System.out.println("**********************");
                System.out.println(schoolClass.getName());
                schoolStudent.setSchoolClass(schoolClass);
                row.setClassName(schoolStudent.getSchoolClass().getName());
            }

        }
        return row;
    }

    /**
     * 根据查询条件导出表格数据
     * 1.如果是模板，则输出标题栏加示例；
     * 2.如果不是模板，则根据查询条件输出所有记录
     *
     * @param isTemplate
     * @param schoolStudentQuery
     * @paramsysUserQuery查询条件
     */
    @Override
    public ExcelWriter exportExcel(boolean isTemplate, SchoolStudentQuery schoolStudentQuery) {
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //设置表格全局样式
        MyHutoolCustomExcelUtil.setBaseGlobalStyle(writer);
        //实现第一个sheet内容
        this.writeExportUserSheet(writer, isTemplate, schoolStudentQuery);
        //添加第二个sheet:班级表
        this.writeExportClassSheet(writer);
        //添加第三个sheet:性别字典表
        this.writeExportSexSheet(writer);
        return writer;
    }

    /**
     * 输出用户列表sheet，默认是第一个sheet
     * 说明：
     * 1.如果是模板，则输出标题栏加示例；
     * 2.如果不是模板，则输出所有记录
     *
     * @param writer
     * @param isTemplate 是否是模板
     */
    private void writeExportUserSheet(ExcelWriter writer, boolean isTemplate, SchoolStudentQuery schoolStudentQuery) {
        // 定义用户记录数集合
        List<StudentRow> rows = new ArrayList<>();
        //自定义账户对象对应的标题别名,因为是LinkedHashMap，此处的keyset返回的是有序的。
//        Set<Map.Entry<String, String>> entryseSet=STUDENTBEAN_MAP.entrySet();
//        for(Map.Entry<String, String> entry:entryseSet){
//            //自定义账户对象对应的标题别名
//            writer.addHeaderAlias(entry.getKey(),entry.getValue());
//        }

        //步骤一：重命名当前工作表名称。
        writer.renameSheet(sheetName);
        //步骤二：设置列宽
        //获取set集合，因为是LinkedHashMap集合，因此转换过来的是有序的set集合
        Set<String> keySet = STUDENTBEAN_MAP.keySet();
        //转为list集合,用于在后面能够根据键值获取所在的列索引（避免直接操作列索引导致的灵活性不足）
        List<String> keyList = new ArrayList<String>(keySet);
        writer.setColumnWidth(keyList.indexOf("className"), 20);
        writer.setColumnWidth(keyList.indexOf("studentNumber"), 20);
        writer.setColumnWidth(keyList.indexOf("name"), 20);
        //设置角色集合列宽
        writer.setColumnWidth(keyList.indexOf("sex"), 20);
        //设置部门名称列宽
        writer.setColumnWidth(keyList.indexOf("national"), 20);
        writer.setColumnWidth(keyList.indexOf("nativePlace"), 30);
        writer.setColumnWidth(keyList.indexOf("address"), 50);
        writer.setColumnWidth(keyList.indexOf("tel"), 20);
        writer.setColumnWidth(keyList.indexOf("identityCard"), 50);
        writer.setColumnWidth(keyList.indexOf("description"), 20);
        writer.setColumnWidth(keyList.indexOf("createTime"), 20);
        writer.setColumnWidth(keyList.indexOf("updateTime"), 20);
        //步骤三：设置标题行的别名Map，以便直接通过Model对象生成表格数据，并且能够在model对象有数据时自动生成标题
        writer.setHeaderAlias(STUDENTBEAN_MAP);
        //步骤四：添加数据行
        //从数据库读取所有班级名
        List<String> classNames = schoolClassMapper.getClassname();
        if (isTemplate) {//如果是模板，则在数据行添加示例数据
            //定义项目示例
            SchoolStudent schoolStudent = new SchoolStudent();
            schoolStudent.setStudentNumber("2222211552");
            schoolStudent.setName("张三");
            schoolStudent.setSex(1);
            schoolStudent.setNational("测试");
            schoolStudent.setDescription("这是一个账户例子，导入时请删除");
            //定义行记录对象,并将对象转换过来
            StudentRow row = this.convertStudentRowToExcelRow(schoolStudent, true);
            //将记录添加到集合
            rows.add(row);
        } else {//如果不是模板，则在数据行添加数据行记录
            List<SchoolStudent> schoolStudents = schoolStudentMapper.findAll(schoolStudentQuery);
            for (int i = 0; i < schoolStudents.size(); i++) {
                SchoolStudent schoolStudent = schoolStudents.get(i);
                //定义行记录对象,并将对象转换过来
                StudentRow row = this.convertStudentRowToExcelRow(schoolStudent, false);
                //将记录添加到集合
                rows.add(row);
            }
        }
        //步骤五：一次性写出内容，使用默认样式，强制输出标题。如果没有额外自定义样式，Hutool工具会自动生成统一的样式。但是如果使用了自定义样式这里就不会变化
        writer.write(rows, true);
//        步骤六：针对特殊单元格进行处理，包括值和特殊样式的处理
        //在数据行范围内为班级列设置部门列表下拉框
//            String[] classArray=classNames.toArray(new String[classNames.size()]);
        String[] classArray = classNames.toArray(new String[classNames.size()]);
        for (int rowIndex = 1; rowIndex <= rows.size(); rowIndex++) {
            writer.addSelect(1, rowIndex, classArray);
        }
        if (isTemplate) {//如果是模板，将第一行样式标为红色，作为示例。必须放在数据都添加后设置才有用。
            // 为第一个单元格赋值，因为采用bean来传值的话，第一个单元格为Long类型，无法进行字符串复制。因此这里手动复制
            writer.writeCellValue(0, 1, "示例：");
            //将原有单元格样式基础上将字体颜色修改为红色（注意看函数说明，这里会保留原有样式）
            CellStyle cellStyle = MyHutoolCustomExcelUtil.createRedCellStyle(writer, HorizontalAlignment.CENTER);
            //设置第一行包含有数据的样式为红色字体样式
            writer.setRowStyleIfHasData(1, cellStyle);
        }
    }

    /**
     * 输出班级sheet
     *
     * @paramwriter
     */
    private void writeExportClassSheet(ExcelWriter writer) {
        //自定义需要读取或写出的Sheet，如果给定的sheet不存在，创建之。
        writer.setSheet("班级表（参考）");
        //将字体恢复为原有样式
        //CellStyle cellStyle= MyHutoolCustomExcelUtil.createNormalCellStyle(writer,HorizontalAlignment.CENTER);
        //必须要清掉上一个表设置的HeaderAlias，否则会混用
        writer.clearHeaderAlias();
        writer.addHeaderAlias("id", "班级编号");
        writer.addHeaderAlias("name", "班级名称");
        //设置班级集合
        List<SchoolClass> classes = schoolClassMapper.findAll(null);//查找所有班级
        // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(writer.getHeaderAlias().size() - 1, "班级列表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(classes, true);
    }

    /**
     * 输出性别sheet
     *
     * @paramwriter
     */
    private void writeExportSexSheet(ExcelWriter writer) {
        //自定义需要读取或写出的Sheet，如果给定的sheet不存在，创建之。
        writer.setSheet("性别字典表（参考）");
        //将字体恢复为原有样式
        //CellStyle cellStyle= MyHutoolCustomExcelUtil.createNormalCellStyle(writer,HorizontalAlignment.CENTER);
        //必须要清掉上一个表设置的HeaderAlias，否则会混用
        writer.clearHeaderAlias();
        writer.addHeaderAlias("id", "编号");
        writer.addHeaderAlias("name", "性别");

        List<SexClass> list = new ArrayList<SexClass>();
        for (Map.Entry<Integer, String> item : SEX_MAP.entrySet()) {
            SexClass sexClass = new SexClass();
            sexClass.setId(item.getKey());
            sexClass.setName(item.getValue());
            list.add(sexClass);
        }

        // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(writer.getHeaderAlias().size() - 1, "性别列表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
    }

    /**
     * 导入账户列表
     * 说明：
     * 1.只有root用户能直接导入账户信息
     *
     * @param inputStream excel文件流
     * @return 返回导入结果
     * @paramschoolStudent
     * @paramuser导入文件的对象
     */
    @Override
    public ExcelImportResult addUsersFromExcel(InputStream inputStream, @NotNull(message = "请先登录") SysUser user) {
        ExcelImportResult result = new ExcelImportResult();
        if (sysUserService.isSyperUser(user.getId())) {//如果是超级用户
            //读取账户信息
            List<SchoolStudent> schoolStudents = validateAndReadExcel(inputStream);
            //从账户信息中将需要批量插入和批量更新的记录进行归类
            List<SchoolStudent> newStudents = new ArrayList<>();//新插入的记录，为了保证插入顺序，并且User重写了equal方法（根据id判断相等），必须使用list
            Set<SchoolStudent> oldStudents = new HashSet<>();//需要更新的记录，因为无所谓顺序，所以用set集合

            List<SysUser> newUsers = new ArrayList<>();
            List<SysUser> oldUsers = new ArrayList<>();

            //由于表格中没有userid所以需要另外查找,为避免重复循环访问数据库,所以在此进行操作
//            SchoolStudent findUserIdStudent = new SchoolStudent();
//            for (SchoolStudent schoolStudent : schoolStudents) {
//                if (schoolStudent.getId() != null) {
//                    //只进行userid的设置
//                    findUserIdStudent.setId(schoolStudent.getId());
//                    findUserIdStudent = schoolStudentMapper.findById(findUserIdStudent);
//                    schoolStudent.setUserId(findUserIdStudent.getUserId());
//                }
//            }
            //查找所有学生
            List<SchoolStudent> schoolStudents1=schoolStudentMapper.findAll(null);
            //数据库列表
            for(SchoolStudent schoolStudent:schoolStudents1){
                if(schoolStudent.getId()!=null){
                    //表格列表
                    for (SchoolStudent excelStudent:schoolStudents){
                        if(excelStudent.getId()!=null){
                            //对比学号是否一致,如果相同则将数据库的userid赋值给表格列表学生
                            if(schoolStudent.getId().equals(excelStudent.getId())){
                                excelStudent.setUserId(schoolStudent.getUserId());
                            }
                        }
                    }
                }
            }



            //将要变更的账户角色信息进行处理
            //为其余属性进行处理
            for (SchoolStudent studentOfEach : schoolStudents) {
                if (studentOfEach.getId() != null) {//如果需要更新的记录
                    oldStudents.add(studentOfEach);
                    SysUser oldUser = new SysUser();
                    oldUser.setId(studentOfEach.getUserId());
                    oldUser.setUsername("S" + studentOfEach.getStudentNumber());
                    //更新学生姓名
                    oldUser.setNickName(studentOfEach.getName());
//                    oldUser.setPassword(SHA.getResult("123456"));
                    oldUser.setSex(studentOfEach.getSex());
                    oldUser.setStatus(1);
                    oldUser.setDescription(studentOfEach.getDescription());
                    oldUsers.add(oldUser);
                    result.setUpdateNumber(result.getUpdateNumber() + 1);

                } else {
                    //设置初始密码为123456
//                    userOfEach.(SHA.getResult("123456"));
                    newStudents.add(studentOfEach);
                    SysUser newUser = new SysUser();
//                    newUser.setId(null);
                    newUser.setUsername("S" + studentOfEach.getStudentNumber());
                    newUser.setPassword(SHA.getResult("123456"));
                    newUser.setNickName(studentOfEach.getName());
                    newUser.setSex(studentOfEach.getSex());
                    newUser.setStatus(1);
                    newUser.setDescription(studentOfEach.getDescription());
                    newUsers.add(newUser);
                    result.setInsertNumber(result.getInsertNumber() + 1);
                    //批量插入角色
                }
            }

            sysUserMapper.saveAllIn(newUsers);//账户添加后会返回主键键ID
            sysUserMapper.updateAllIn(oldUsers);
            //循环将学生表 userId 补全
//            newStudents.forEach(System.out::println);
            for (SchoolStudent student : newStudents) {

                for (SysUser newUserEach : newUsers) {
                    if (newUserEach.getUsername().contains("S" + student.getStudentNumber())) {//循环账户信息 如果学号相同则将 user表主键ID 设置给学生
//                        System.out.println(newUserEach);
                        student.setUserId(newUserEach.getId());
                    }
                }
            }
            List<SysUserRole> userRolesOfAdd = new ArrayList<>();//定义要批量添加的用户角色关系
            //对新角色进行角色处理，放在这里的原因是此时已经完成数据的添加，获取到了新插入记录的主键，才能进行关系赋予。
            for (SysUser userOfEach : newUsers) {
                SysUserRole userRole = new SysUserRole();
                userRole.setSysUser(userOfEach);
                userRole.setSysRole(new SysRole(-12));
                userRolesOfAdd.add(userRole);
            }
            //批量添加账户角色(学生)关系
            sysUserRoleMapper.saveAllIn(userRolesOfAdd);

            //批量插入记录
            schoolStudentMapper.saveAllIn(newStudents);
            System.out.println(newStudents);
            //批量编辑记录
            schoolStudentMapper.updateAllIn(oldStudents);
            System.out.println(oldStudents);
        } else {
            throw new AccessDeniedException("只有超级用户才能导入");
        }
        return result;
    }

    /**
     * 验证表中的字段并返回用户集合
     * 说明：用户创建时间、最后编辑时间不导入
     *
     * @param inputStream
     */
    public List<SchoolStudent> validateAndReadExcel(InputStream inputStream) {
        //创建用户对象集合
        List<SchoolStudent> schoolStudents = new ArrayList<>();
        //创建用户对象集合
        List<SchoolClass> schoolClasses = new ArrayList<>();

        //定义异常集合
        List<ExcelExceptionMessage> excelExceptions = new ArrayList<ExcelExceptionMessage>();
        //获取工作簿对象
        ExcelReader excelReader = ExcelUtil.getReader(inputStream);
        //ExcelReader excelReader = ExcelUtil.getReader(inputStream,"账户列表");
        //获取所有工作表对象
        List<Sheet> sheets = excelReader.getSheets();
        boolean containsSheet = false;//是否包含指定sheet
        for (Sheet sheet : sheets) {//遍历所有工作表
            if (sheet.getSheetName().equals(sheetName)) {// 如果sheet名为指定的名称
                containsSheet = true;
                //根据该工作表重新创建一个ExcelReader对象
                excelReader = new ExcelReader(sheet);
                //首先判断表中的账户名是否存在重复信息
                if (MyTableUtil.validateRepeatColumn(excelReader.readColumn(2, 1, excelReader.getRowCount()))) {
                    throw new MyServiceException("导入失败：表格中的账户名存在重复字段");
                }
//                //首先判断表中的网名是否存在重复信息
//                if (MyTableUtil.validateRepeatColumn(excelReader.readColumn(2, 1, excelReader.getRowCount()))) {
//                    throw new MyServiceException("导入失败：表格中的昵称存在重复字段");
//                }
                //准备录入数据中要多次查找数据库的数据，避免后面遍历时重复查找，提升性能
                List<SchoolClass> classes = schoolClassMapper.findAll(null);

                //获取set集合，因为是LinkedHashMap集合，因此转换过来的是有序的set集合
                Collection<String> valueSet = STUDENTBEAN_MAP.values();
                //转为list集合,用于在后面能够根据键获取所在的列索引
                List<String> keyList = new ArrayList<String>(valueSet);
                //读取第二行到最后一行数据
                //List<List<Object>> rows = excelReader.read(1, excelReader.getRowCount());
                List<Map<String, Object>> rows = excelReader.read(0, 1, excelReader.getRowCount());
                //遍历每行的数据
                for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
                    SchoolStudent schoolStudent = new SchoolStudent();
                    Map<String, Object> row = rows.get(rowIndex);//获取当前行的数据
                    //第1列：账户id。如果id为空，表示该账户是要插入的账户；否则则是要更新相关信息
                    String userIdString = String.valueOf(row.get("用户编号"));
                    //如果id存在则赋值
                    if (StringUtils.hasLength(userIdString)) {
                        try {
                            schoolStudent.setId(Long.valueOf(userIdString));
                            //验证id是否在数据库存在，屏蔽无效id。这里用count，提升性能
                            SchoolStudentQuery schoolStudentQuery = new SchoolStudentQuery();
                            schoolStudentQuery.setId(schoolStudent.getId());
                            if (schoolStudentMapper.count(schoolStudentQuery) == 0) {
                                excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("用户编号"), userIdString, "用户主键在系统中不存在"));
                            }
                        } catch (NumberFormatException e) {
                            excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("用户编号"), userIdString, "用户主键必须为整数"));
                        }
                    }

                    //第2列:班级名称
                    String ClassName = String.valueOf(row.get("班级名称"));
                    if (StringUtils.hasLength(ClassName)) {
                        boolean exist = false;//默认该部门不存在
                        for (SchoolClass schoolClass : classes) {
                            if (schoolClass.getName().equals(ClassName)) {
                                exist = true;
                                schoolStudent.setSchoolClass(schoolClass);
                                break;
                            }
                        }
                        if (!exist) {
                            excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("班级名称"), ClassName, ClassName + "在系统中不存在"));
                        }
                    } else {
                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("班级名称"), ClassName, "必须选择班级"));
                    }
                    //第3列：学号
                    String studentNumber = String.valueOf(row.get("学号"));
                    //如果账户名学号则赋值
                    if (StringUtils.hasLength(studentNumber)) {
                        schoolStudent.setStudentNumber(studentNumber);
                        if (schoolStudent.getId() != null) {//如果不存在说明是要编辑，则要判定是否重名
                            if (schoolStudentMapper.countByStudentNumberAndNotId(studentNumber, schoolStudent.getId()) > 0) {//判断数据库中的账户是否重名
                                excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("学号"), studentNumber, "学号在系统中重复"));
                            }
                        } else {//如果是要插入账户
                            if (schoolStudentMapper.countByStudentNumberAndNotId(studentNumber, schoolStudent.getId()) > 0) {//判断数据库中的账户是否重名
                                excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("学号"), studentNumber, "学号在系统中重复"));
                            }
                            if (sysUserMapper.findOneByUsername("S" + studentNumber) != null) {
                                excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("学号"), studentNumber, "该学生账号在系统中重复"));
                            }
                        }
                    } else {
                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("学号"), studentNumber, "学号不能为空"));
                    }
                    //第3列：昵称
                    String nickName = String.valueOf(row.get("名字"));
                    //如果账户名存在则赋值
                    if (StringUtils.hasLength(nickName)) {
                        schoolStudent.setName(nickName);
//                        if (schoolStudent.getId() != null) {//如果不存在说明是要编辑，则要判定是否重名
//                            if (schoolStudentMapper.countByNameAndNotId(nickName, schoolStudent.getId()) > 0) {//判断数据库中的昵称是否重名
//                                excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("名字"), nickName, "昵称和系统中的重名"));
//                            }
//                        } else {//如果是要插入账户
//                            if (schoolStudentMapper.countNickName(nickName) > 0) {//判断数据库中的昵称是否重名
//                                excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("名字"), nickName, "昵称和系统中的重名"));
//                            }
//                        }
                    } else {
                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("名字"), nickName, "昵称不能为空"));
                    }
                    //第4列：性别
                    String sexString = String.valueOf(row.get("性别"));
                    if (StringUtils.hasLength(sexString)) {
                        Collection<String> values = SEX_MAP.values();
                        if (values.contains(sexString)) {//如果包含的值一样
                            //使用hutool的工具集合进行map双向查找
                            BiMap<Integer, String> biMap = new BiMap<>(SEX_MAP);
                            schoolStudent.setSex(biMap.getKey(sexString));
                        } else {
                            String sexValues = values.stream().map(String::valueOf).collect(Collectors.joining(","));
                            excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("性别"), sexString, "性别的值只能是:" + sexValues));
                        }
                    } else {
                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("性别"), sexString, "性别不能为空"));
                    }
                    //第5列：民族
                    String national = String.valueOf(row.get("民族"));

                    if (StringUtils.hasLength(national)) {
                        schoolStudent.setNational(national);
                    }
//                    else {
//                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("民族"), national, "民族不能为空"));
//                    }
                    //第6列：籍贯
                    String nativePlace = String.valueOf(row.get("籍贯"));

                    if (StringUtils.hasLength(nativePlace)) {
                        schoolStudent.setNativePlace(nativePlace);
                    }
//                    else {
//                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("籍贯"), nativePlace, "籍贯不能为空"));
//                    }

                    //第7列:地址
                    String address = String.valueOf(row.get("地址"));

                    if (StringUtils.hasLength(address)) {
                        schoolStudent.setAddress(address);
                    }
//                    else {
//                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("地址"), address, "地址不能为空"));
//                    }
                    //第8列:电话
                    String tel = String.valueOf(row.get("电话"));

                    if (StringUtils.hasLength(tel)) {
                        schoolStudent.setTel(tel);

                    } else {
                        excelExceptions.add(new ExcelExceptionMessage(sheet.getSheetName(), rowIndex + 1, keyList.indexOf("电话"), tel, "电话不能为空"));
                    }
//                    writer.setColumnWidth(keyList.indexOf("identityCard"),50);
                    //第9列:电话
                    String identityCard = String.valueOf(row.get("身份证号"));
                    if (StringUtils.hasLength(identityCard)) {
                        schoolStudent.setIdentityCard(identityCard);
                    }
                    ;

                    //账户描述
                    schoolStudent.setDescription(String.valueOf(row.get("账户描述")));
                    //将账户添加进集合
                    schoolStudents.add(schoolStudent);
                }
            }
        }
        if (!containsSheet) {//如果指定表不存在
            throw new MyServiceException("导入失败：表格中必须有名称为" + sheetName + "的sheet");
        }
        if (excelExceptions.size() > 0) {//如果存在异常
            throw new MyExcelException(excelExceptions);
        }
        return schoolStudents;
    }

    //定义表格的行对象，用于处理表格中的bean
    @Data
    private class StudentRow {
        private Long id;
        private String className;//班级名称
        private String studentNumber;//学号
        private String name;//名字
        private String sex;//性别
        private String national;//民族
        private String nativePlace;//籍贯
        private String address;//地址
        private String tel;//电话
        private String identityCard;//身份证号
        private String description;//账户描述
        private Date createTime;//创建时间
        private Date updateTime;//最后编辑时间
    }

    @Data
    private class SexClass {
        private Integer id;
        private String name;
    }

}
